package idv.arthur.work;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

/**
 * Servlet implementation class QueryTC
 */
//@WebServlet(description = "查詢測試案例", urlPatterns = { "/QueryTC" })
public class QueryTC extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public QueryTC() {
        super();
    }

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		String q_tcID = request.getParameter("q_tcID");					//測試案例ID
		String q_tcName = request.getParameter("q_tcName");				//測試案例名稱
		String q_parentTCID = request.getParameter("q_parentTCID");		//父測試案例名ID
		String q_creator = request.getParameter("q_creator");			//創建者
		String q_fromDate = request.getParameter("q_fromDate");			//創建日期(起)
		String q_toDate = request.getParameter("q_toDate");				//創建日期(迄)
		
		
		String strSQL = "SELECT t3.nickName, t2.uID,t2.tcID,t1.tcName,t1.tcKeyPoint,t1.tcProcess,t1.tcResExp,t1.CreatedDateTime,t1.P_tcID,t1.P_UID,t1.Creator FROM tms_testcase t1, tms_tcidmapping t2,tms_personnel t3 WHERE t1.Creator=t3.userID AND t1.uID=t2.uID AND t2.active=1 AND ";
		
		if (q_tcID != null && !q_tcID.trim().equals("")) {
			strSQL += "t2.tcID='" + q_tcID + "' AND ";
		} 
		
		if (q_tcName != null && !q_tcName.trim().equals("")) {
			strSQL += "t1.tcName='" + q_tcName + "' AND ";
		}
		if (q_parentTCID != null && !q_parentTCID.trim().equals("")) {
			strSQL += "t1.P_tcID='" + q_parentTCID + "' AND ";
		}
		if (q_creator != null && !q_creator.trim().equals("")) {
			strSQL += "t1.Creator='" + q_creator + "' AND ";
		}
		
		if ((q_fromDate != null && !q_fromDate.trim().equals(""))&&(q_toDate != null && !q_toDate.trim().equals(""))) {			//兩個都有輸入
			strSQL += "t1.CreatedDateTime BETWEEN '" + q_fromDate + "' AND '" + q_toDate +"'";;
		} else if ((q_fromDate != null && !q_fromDate.trim().equals(""))&&!(q_toDate != null && !q_toDate.trim().equals(""))) {	//僅有起
			strSQL += "t1.CreatedDateTime BETWEEN '" + q_fromDate + "' AND '" + "2020-12-31'";
		} else if (!(q_fromDate != null && !q_fromDate.trim().equals(""))&&(q_toDate != null && !q_toDate.trim().equals(""))) {	//僅有迄
			strSQL += "t1.CreatedDateTime BETWEEN '" + "1999-12-31" + "' AND '" + q_toDate +"'";
		} else {
			strSQL = strSQL.substring(0, strSQL.length()-4);		//皆無，將尾巴的 AND 拿掉
		}
		
		Connection conn = null;
		Widgets wid = new Widgets();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<TestCase> testCases = new ArrayList<TestCase>();
		String tt = "";
		
		
		try {
			conn = wid.getConn(true);
			ps = conn.prepareStatement(strSQL);
			rs = ps.executeQuery();
			while (rs.next()) {
				TestCase testCase = new TestCase();
				testCase.setuID(rs.getInt("uID"));
				testCase.setTcID(rs.getString("tcID"));
				testCase.setTcName(rs.getString("tcName"));
				testCase.setTcKeyPoint(rs.getString("tcKeyPoint"));
				testCase.setTcProcess(rs.getString("tcProcess"));
				testCase.setTcResExp(rs.getString("tcResExp"));
				testCase.setCreatedDateTime(rs.getDate("CreatedDateTime").toString());
				testCase.setCreator(rs.getString("nickName")+"("+rs.getString("Creator")+")");
				testCases.add(testCase);
			}
			if (testCases.size() > 0) {
				tt = new Gson().toJson(testCases);
				tt = tt.replace("[", "{\"sqlData\":[")+ ",\"strSQL\":[{\"strSQL\":\""+strSQL+"\"}]}";
			} else {
				tt = new Gson().toJson(testCases);
				tt = tt.replace("[", "{\"sqlData\":[")+ ",\"strSQL\":[{\"result\":\"No Result\"}]}";
			}
		} catch (SQLException e) {
			tt = "{\"errorMsg\":\""+wid.keepErrorMes(conn, ps, e, "", false)+"\"}";
		} finally {
			try {
				if (rs!= null) {rs.close();}
				if (ps!= null) {ps.close();}
				if (conn!= null) {conn.close();}
			} catch (SQLException e) {
				tt = "{\"errorMsg\":\""+e.toString()+"\"}";
			}
		} 
		
		response.setContentType("application/json");
		response.setCharacterEncoding("UTF-8");
		response.getWriter().write(tt);
	}
}